/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sqlgenerators.search;

import entities.Search;

/**
 *
 * @author damfercer
 */
public class BasicSearchSQLGenerator implements SearchSqlGeneratorInterface{
    private String sqlRes;
    
    public BasicSearchSQLGenerator(){
        sqlRes="";
    }
    
    @Override
    public String getSqlFromSearch(Search search) {
       sqlRes="";
       if(search.getCompanyAdquiredProfileFolderState()!=null){
           sqlRes+=" AND company_adquired_profile_folder.COMPANY_ADQUIRED_PROFILE_FOLDER_STATE="+search.getCompanyAdquiredProfileFolderState().getIdCompanyAdquiredProfileFolderState();
       }
       if(search.getSex()!=null){
           sqlRes+=" AND candidate.SEX="+search.getSex().getIdSex();
       } 
       if(search.getMinimumAge()!=null && search.getMinimumAge()!=0){
           sqlRes+=" AND (DATEDIFF(NOW(),candidate.BIRTH_DAY)/365)>"+search.getMinimumAge();
       }
       if(search.getMaximumAge()!=null && search.getMaximumAge()!=0){
           sqlRes+=" AND (DATEDIFF(NOW(),candidate.BIRTH_DAY)/365)<"+search.getMaximumAge();
       }
       if(search.getOfferCountry()!=null){
           if(search.getOfferProvince()!=null){
               sqlRes+=" AND (candidate.PROVINCE="+search.getOfferProvince().getIdProvince()+" OR candidate_relocating_info.PROVINCE="+search.getOfferProvince().getIdProvince()+" OR (candidate_relocating_info.COUNTRY="+search.getOfferCountry().getIdCountry()+" AND candidate_relocating_info.PROVINCE IS NULL) )";
           }
           else{
               sqlRes+=" AND candidate_relocating_info.COUNTRY="+search.getOfferCountry().getIdCountry()+" AND candidate_relocating_info.PROVINCE IS NULL";
           }
       }
       
       if(search.getProfessionalInfoSector()!=null){
           sqlRes+=" AND candidate_professional_info.SECTOR="+search.getProfessionalInfoSector().getIdSector();
       }
       if(search.getProfessionalInfoMinimumJobDuration()!=null){
           sqlRes+=" AND job_duration.VALUE>="+search.getProfessionalInfoMinimumJobDuration().getValue();
       }
       if(search.getEducationalInfoMinimumEducationalLevel()!=null){
           sqlRes+=" AND educational_level.EDUCATIONAL_VALUE>="+search.getEducationalInfoMinimumEducationalLevel().getEducationalValue();
       }
       if(search.getEducationalInfoSector()!=null){
           sqlRes+=" AND candidate_educational_info.SECTOR="+search.getEducationalInfoSector().getIdSector();
       }
       return sqlRes;
    }

    /**
     * @return the sqlRes
     */
    public String getSqlRes() {
        return sqlRes;
    }

    /**
     * @param sqlRes the sqlRes to set
     */
    public void setSqlRes(String sqlRes) {
        this.sqlRes = sqlRes;
    }
    
}
